package worklog.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import worklog.dto.UserDTO;



public class UserDB {
	private MyDB db;

    public UserDB(){ 
    	try { db = new MyDB(); }
    	catch(Exception e){ db = null; }
    }
	protected void finalize(){ db.close(); }

	public void close(){ db.close(); }
	
	public Boolean isReady(){ return (this.db!=null); }
	
	/**
	 * Check user login credentials
	 * @throws SQLException 
	 */
	public UserDTO LoginUser(String name, String pass) throws SQLException{
		PreparedStatement statement = null;
		ResultSet res = null;
		UserDTO user = null;

		statement = db.connection.prepareStatement("SELECT * FROM user WHERE login = ? AND (password = md5(?) OR md5(concat(id,login,password)) = ?)");
		statement.setString(1, name);
		statement.setString(2, pass);
		statement.setString(3, pass);
		res = statement.executeQuery();			
		if (res != null && res.next()){
			user = new UserDTO();
			user.setAll(res);
		}
		res.close();
		statement.close();
		
		return user;
	}
	
	/**
	 * Retrieve user by name
	 */
	public UserDTO getUser(String name){
		PreparedStatement statement = null;
		ResultSet res = null;
		UserDTO user = new UserDTO();
		try {
			statement = db.connection.prepareStatement("SELECT * FROM user WHERE login = ?");
			statement.setString(1, name);
			res = statement.executeQuery();			
			if (res != null && res.next()) user.setAll(res);		// include password to compare when logging-in
			res.close();
			statement.close(); 
		} 
		catch (SQLException e){ e.printStackTrace(); }		
		return user;
	}
	
	/**
	 * Retrieve user by ID
	 */
	public UserDTO getUserById(int id){
		PreparedStatement statement = null;
		ResultSet res = null;
		UserDTO user = new UserDTO();
		try {
			statement = db.connection.prepareStatement("SELECT * FROM user WHERE id = ?");
			statement.setInt(1, id);
			res = statement.executeQuery();			
			if (res != null && res.next()) user.set(res);		// don't include password for jsp output
			res.close();
			statement.close(); 
		} 
		catch (SQLException e){ e.printStackTrace(); }		
		return user;
	}
	
	
	/**
	 * Check whether user is an admin
	 */
	public Boolean isAdmin(int id){
		PreparedStatement statement = null;
		ResultSet res = null;
		UserDTO user = new UserDTO();
		try {
			statement = db.connection.prepareStatement("SELECT * FROM user WHERE id = ?");
			statement.setInt(1, id);
			res = statement.executeQuery();			
			if (res != null && res.next()) user.set(res);
			res.close();
			statement.close(); 
		} 
		catch (SQLException e){ e.printStackTrace(); }
		if (user != null && user.getStatus() == 2) return true;
		return false;
	}
	
	
	/**
	 * Update user details
	 */
	public int updateUser(UserDTO user){
		PreparedStatement stat = null;
		int res = -1;
		try {
			stat = db.connection.prepareStatement("UPDATE user SET login = ?, email = ?, status = ? WHERE id = ?");
			stat.setString(1, user.getLogin());
			stat.setString(2, user.getEmail());
			stat.setInt(3, user.getStatus());
			stat.setInt(4, user.getId());
			res = stat.executeUpdate();
			stat.close();
		} 
		catch (SQLException e){
			e.printStackTrace();
		}
		return res;
	}	

	/**
	 * Update user password
	 */
	public int updateUserPassword(UserDTO user){
		PreparedStatement stat = null;
		int res = -1;
		try {
			stat = db.connection.prepareStatement("UPDATE user SET password = md5(?) WHERE id = ?");
			stat.setString(1, user.getPassword());
			stat.setInt(2, user.getId());
			res = stat.executeUpdate();
			stat.close();
		} 
		catch (SQLException e){
			e.printStackTrace();
		}
		return res;
	}	
	
	
	/**
	 * Remove user from the DB
	 */
	public int deleteUser(int userId){
		PreparedStatement stat = null;
		int res = -1;
		try {
			stat = db.connection.prepareStatement("DELETE FROM user WHERE id = ?");
			stat.setInt(1, userId);
			res = stat.executeUpdate();
			stat.close();
		} 
		catch (SQLException e){}
		return res;
	}	

	
	/**
	 * Add new user
	 * @return	new user ID
	 */
	public int addUser(UserDTO user){
		PreparedStatement statement = null;
		int id = -1;
		try {
			statement = db.connection.prepareStatement("INSERT INTO user (login, password, email, ip, status) VALUES (?, '', ?, '', ?)", PreparedStatement.RETURN_GENERATED_KEYS);
			statement.setString(1, user.getLogin());
			statement.setString(2, user.getEmail());
			statement.setInt(3, user.getStatus());
			statement.executeUpdate();
			ResultSet rskey = statement.getGeneratedKeys();
			if (rskey != null && rskey.next()) id = rskey.getInt(1);
			statement.close(); 
		} 
		catch (SQLException e){
			e.printStackTrace();
		}
		return id;
	}	
	
	
	/**
	 * Check if project name already exist
	 */
	public Boolean checkUserLogin(String login){
		PreparedStatement statement = null;
		ResultSet res = null;
		Boolean userExists = false;
		try {
			statement = db.connection.prepareStatement("SELECT * FROM user WHERE login = ?");
			statement.setString(1, login);
			res = statement.executeQuery();			
			if (res != null && res.next()) userExists = true;
			res.close();
			statement.close(); 
		} 
		catch (SQLException e){
			e.printStackTrace();
		}		
		return userExists;
	}
	
	/**
	 * Check if user old password matches
	 */
	public Boolean checkUserPassword(int id, String password){
		PreparedStatement statement = null;
		ResultSet res = null;
		Boolean userExists = false;
		try {
			statement = db.connection.prepareStatement("SELECT * FROM user WHERE id = ? AND password = md5(?)");
			statement.setInt(1, id);
			statement.setString(2, password);
			res = statement.executeQuery();			
			if (res != null && res.next()) userExists = true;
			res.close();
			statement.close(); 
		} 
		catch (SQLException e){
			e.printStackTrace();
		}		
		return userExists;
	}
	
	
	/**
	 * Retrieves all users
	 * @throws SQLException 
	 */
	public ArrayList<UserDTO> getUsers() throws SQLException {
		ArrayList<UserDTO> userList = new ArrayList<UserDTO>();
		
		Statement statement = db.connection.createStatement();
		ResultSet res = statement.executeQuery("SELECT * FROM user ORDER BY login");

		while (res.next()){
			UserDTO user = new UserDTO();
			user.set(res);
			userList.add(user);
		}

		statement.close();
		
		if (userList.size() > 0) return userList;
		return null;
	}
	
}
